package com.csmy.my.center.util.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.attilax.biz.seo.getConnEx;
import com.attilax.exception.ExUtil;
import com.csmy.my.center.util.CTUtils;
import com.csmy.my.center.util.StringUtil;
import com.csmy.my.center.util.UniqueID;
import com.csmy.my.center.util.dataconvert.Dto;
import com.csmy.my.center.util.dataconvert.impl.BaseDto;

/**
 * 连接各类数据库的方法
 */
public class DBConnect {
	/**
	 * 获得数据库连接
	 * @param driverClassName	连接数据库用到的驱动类的类名
	 * @param dbURL		数据库的URL
	 * @param userName	登陆数据库的用户名
	 * @param password	登陆数据库的密码
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection(String driverClassName,
			String dbURL, String userName, String password)
			throws ClassNotFoundException, SQLException {
		Connection con = null;

		// 加载连接数据库的驱动类
		Class.forName(driverClassName);
		// 用用户名、密码连接数据库
		con = DriverManager.getConnection(dbURL, userName, password);

		return con;
	}
	
	/**
	 * 获得Oracle数据库的连接
	 * @param dricerClassName	连接数据库用到的驱动类的类名
	 * @param serverHost	数据库所在服务器的IP或域名
	 * @param serverPort	数据库所在服务器的端口
	 * @param dbName		数据库名
	 * @param userName		登陆数据库的用户名
	 * @param password		登陆数据库的密码
	 * @return
	 * @throws ClassNotFoundException		数据库驱动类无法找到是抛出该异常
	 * @throws SQLException		创建连接时可能抛出该异常
	 */
	public static Connection getOracleConnection(String dricerClassName,
			String serverHost, String serverPort, String dbName,
			String userName, String password) throws ClassNotFoundException,
			SQLException {
		// 如果没有提供这些连接参数，则用默认值
		if (dricerClassName == null) {
			dricerClassName = "oracle.jdbc.driver.OracleDriver";
		}
		if (serverHost == null) {
			serverHost = "127.0.0.1";
		}
		if (serverPort == null) {
			serverPort = "1521";
		}
		// 构建访问Oracle数据库的URL
		String dbURL = "jdbc:oracle:thin:@" + serverHost + ":" + serverPort
				+ ":" + dbName;
		return getConnection(dricerClassName, dbURL, userName, password);
	}
	
	/**
	 * 获得DB2数据库的连接
	 */
	public static Connection getDB2Connection(String dricerClassName,
			String serverHost, String serverPort, String dbName,
			String userName, String password) throws ClassNotFoundException,
			SQLException {
		// 如果没有提供这些连接参数，则用默认值
		if (dricerClassName == null) {
			dricerClassName = "com.ibm.db2.jdbc.app.DB2Driver";
		}
		if (serverHost == null) {
			serverHost = "127.0.0.1";
		}
		if (serverPort == null) {
			serverPort = "5000";
		}
		// 构建访问DB2数据库的URL
		String dbURL = "jdbc:db2://" + serverHost + ":" + serverPort
				+ "/" + dbName;
		return getConnection(dricerClassName, dbURL, userName, password);
	}
	
	/**
	 * 获得SQL Server数据库的连接
	 */
	public static Connection getSQLServerConnection(String dricerClassName,
			String serverHost, String serverPort, String dbName,
			String userName, String password) throws ClassNotFoundException,
			SQLException {
		// 如果没有提供这些连接参数，则用默认值
		if (dricerClassName == null) {
			dricerClassName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
		}
		if (serverHost == null) {
			serverHost = "127.0.0.1";
		}
		if (serverPort == null) {
			serverPort = "1433";
		}
		// 构建访问SQL Server数据库的URL
		String dbURL = "jdbc:microsoft:sqlserver://" + serverHost + ":" + serverPort
				+ "; DatabaseName=" + dbName;
		return getConnection(dricerClassName, dbURL, userName, password);
	}
	
	/**
	 * 获得MySQL数据库的连接
	 */
	public static Connection getMySQLConnection(String dricerClassName,
			String serverHost, String serverPort, String dbName,
			String userName, String password) throws ClassNotFoundException,
			SQLException {
		// 如果没有提供这些连接参数，则用默认值
		if (dricerClassName == null) {
			dricerClassName = "com.mysql.jdbc.Driver";
		}
		if (serverHost == null) {
			serverHost = "127.0.0.1";
		}
		if (serverPort == null) {
			serverPort = "3306";
		}
		// 构建访问SQL Server数据库的URL
		String dbURL = "jdbc:mysql://" + serverHost + ":" + serverPort
				+ "/" + dbName;
		return getConnection(dricerClassName, dbURL, userName, password);
	}
	
	/**
	 * 获得Sybase数据库的连接
	 */
	public static Connection getSybaseConnection(String dricerClassName,
			String serverHost, String serverPort, String dbName,
			String userName, String password) throws ClassNotFoundException,
			SQLException {
		// 如果没有提供这些连接参数，则用默认值
		if (dricerClassName == null) {
			dricerClassName = "com.sybase.jdbc3.jdbc.SybDriver";
		}
		if (serverHost == null) {
			serverHost = "127.0.0.1";
		}
		if (serverPort == null) {
			serverPort = "5007";
		}
		// 构建访问SQL Server数据库的URL
		String dbURL = "jdbc:sybase:Tds:" + serverHost + ":" + serverPort
				+ "/" + dbName;
		return getConnection(dricerClassName, dbURL, userName, password);
	}
	
	/**
	 * 获得PostgreSQL数据库的连接
	 */
	public static Connection getPostgreSQLConnection(String dricerClassName,
			String serverHost, String serverPort, String dbName,
			String userName, String password) throws ClassNotFoundException,
			SQLException {
		// 如果没有提供这些连接参数，则用默认值
		if (dricerClassName == null) {
			dricerClassName = "org.postgresql.Driver";
		}
		if (serverHost == null) {
			serverHost = "127.0.0.1";
		}
		if (serverPort == null) {
			serverPort = "5432";
		}
		// 构建访问SQL Server数据库的URL
		String dbURL = "jdbc:postgresql://" + serverHost + ":" + serverPort
				+ "/" + dbName;
		return getConnection(dricerClassName, dbURL, userName, password);
	}
	
	/**
	 * 获取jdbc连接
	 * 
	 * @return
	 */
	public static Connection getMysqlConnect() {
		Connection con = null;
		Context context = null;
		DataSource ds = null;
		String jndiConfig = null;
		InputStream ins = null;
		Properties prop = new Properties();
		try {
		
			ins = DBConnect.class.getResourceAsStream("/jdbc.properties");
			prop.load(ins);
			
			//ati p213
			con = getconn(prop);
//			jndiConfig = prop.getProperty("ds");
//			context = new InitialContext();
//			ds = (DataSource) context.lookup(jndiConfig);
//			con = ds.getConnection();
			//StringUtil.xprint("获取mysql数据库连接成功！");
		} catch (Exception e) {
			StringUtil.xprint("第一次获取jdbc连接失败");
			e.printStackTrace();
			try {
//				context = new InitialContext();
//				ds = (DataSource) context.lookup(jndiConfig);
//				con = ds.getConnection();
				con = getconn(prop);
			} catch (Exception e1) {
				StringUtil.xprint("第二次获取jdbc连接失败");
				e.printStackTrace();
				try {
//					context = new InitialContext();
//					ds = (DataSource) context.lookup(jndiConfig);
//					con = ds.getConnection();
					con = getconn(prop);
				} catch (Exception e2) {
					StringUtil.xprint("第三次获取jdbc连接失败");
					e.printStackTrace();
					try {
//						context = new InitialContext();
//						ds = (DataSource) context.lookup(jndiConfig);
//						con = ds.getConnection();
						con = getconn(prop);
					} catch (Exception e3) {
						StringUtil.xprint("第四次获取jdbc连接失败");
						e.printStackTrace();
						closeProp(ins);
						ExUtil.throwEx(e3);
						 
							  
					}
				}
			}
		} finally {
			closeProp(ins);
		}
		return con;
	}

	private static void closeProp(InputStream ins) {
		try {
			ins.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static Connection getconn(Properties prop) throws getConnEx {
		Connection con;
		try {
			
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						throw new getConnEx("getconnex" + e.getMessage());
					}
		Connection conn;
		try {
			conn = DriverManager.getConnection(
					prop.getProperty("ct.jdbc.url"),
					prop.getProperty("ct.jdbc.username"),
					prop.getProperty("ct.jdbc.password"));
		} catch (SQLException e) {
			throw new getConnEx("getconnex" + e.getMessage());
		}
		con=conn;
		return con;
	}

	
	/**
	 * 使用Statement查询数据，返回List集合，数据量比较小的时候用
	 * @param sql
	 * @return
	 */
	public static List<Dto> queryForList(String sql,Connection con) {
		ResultSet rs = null;
		Statement sta = null;
		List<Dto> dtoList = null;
		try {
			sta = con.createStatement();
			rs = sta.executeQuery(sql);
			Dto mapDto = null;
			ResultSetMetaData rsmd = rs.getMetaData();
			int rowCnt = rsmd.getColumnCount();
			dtoList = new ArrayList<Dto>();
			while (rs.next()) {
				mapDto = new BaseDto();
				for (int i = 1; i <= rowCnt; i++) {
					mapDto.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				dtoList.add(mapDto);
			}
		} catch (SQLException e) {
			StringUtil.xprint("jdbc查询数据返回list方法报错"); // 异常描述
			e.printStackTrace(); // 输出堆栈信息
		}
		return dtoList;
	}
	
	public static void main(String[] args) throws ClassNotFoundException, 
			SQLException {

		//String OracleDirver = "oracle.jdbc.driver.OracleDriver";
		String mySQLDirver = "com.mysql.jdbc.Driver";
		String dbName = "wechatdb";
		String userName = "root";
		String password = "520520";
		//String url ="jdbc:oracle:thin:@10.0.0.119:1521:orcl";
		//Connection con = DBConnect.getConnection(driverClassName, url, userName, password);
        //Connection con = DBConnect.getOracleConnection(OracleDirver, "127.0.0.1", "1521", dbName, userName, password);
		//System.out.println("连接Oracle数据库成功！");
		//con.close();
		//System.out.println("成功关闭与Oracle数据库的连接！");
		//jdbc:mysql://192.168.1.75/wy_web_multi_1228
		String url = "jdbc:mysql://127.0.0.1:3306/" +  dbName;
		Connection con = DBConnect.getConnection(mySQLDirver, url, userName, password);
		String sql=" select "+
			" t.id, "+
			" t.user_login as account, "+
			" t.user_qq as qq_num, "+
			" t.user_email as email, "+
			" t.user_mobile as phone, "+
			" t.create_time as register_time, "+
			" t.user_alipay as pay_account, "+
			" t.user_alipayname as name, "+
			" t.toplink as top_id "+
			" from vi_users t where t. user_type = 2 ";
		List<Dto> mList = queryForList(sql,con);
		StringUtil.xprint("sql=="+sql);
		StringUtil.xprint("mocunt=="+mList.size());
		//开始处理数据
		String sqls[] = new String[mList.size()];;
		String sqlString = null;
		String mid = null;
		String vcode = null;
		int count = 0;
		String stime = null;
		for (Dto dto : mList) {
			mid = UniqueID.getUniqueID(8,2);
			vcode = UniqueID.getUniqueID(5,2);
			StringUtil.xprint("top_id="+dto.getAsString("top_id"));
			sqlString = "INSERT INTO wxb_memeber (recom_user,level_code,use_recom,password,memeber_id,visit_code,id, account, qq_num, email, phone, register_time, pay_account, name, top_id) VALUES (";
			sqlString += "null,";
			sqlString += "'"+vcode+"',1,'byHQ+jOqWSc=',";
			sqlString += "'"+mid+"',";
			sqlString += "'"+vcode+"',";
			sqlString += "'"+dto.getAsString("id")+"',";
			sqlString += "'"+dto.getAsString("user_login")+"',";
			sqlString += "'"+dto.getAsString("user_qq")+"',";
			sqlString += "'"+dto.getAsString("user_email")+"',";
			sqlString += "'"+dto.getAsString("user_mobile")+"',";
			stime = dto.getAsString("create_time");
			if(CTUtils.isEmpty(stime)){
				stime = CTUtils.getCurrentTime();
			}
			sqlString += "'"+stime+"',";
			sqlString += "'"+dto.getAsString("user_alipay")+"',";
			sqlString += "'"+dto.getAsString("user_alipayname")+"',";
			sqlString += "'"+dto.getAsString("toplink")+"')";
			sqls[count] = sqlString;
			count++;
		}
		
		// 判断是否支持批处理
		boolean supportBatch = supportBatch(con);
		con.setAutoCommit(false);
		if (supportBatch) {
			// 执行一批SQL语句
			int[] results = goBatch(con, sqls);
			con.commit();
			con.setAutoCommit(true);
			// 分析执行的结果
			for (int k = 0; k < sqls.length; k++) {
				if (results[k] >= 0) {
					System.out.println("语句: " + sqls[k] + " 执行成功，影响了" + results[k] + "行数据");
				} else if (results[k] == Statement.SUCCESS_NO_INFO) {
					System.out.println("语句: " + sqls[k] + " 执行成功，影响的行数未知");
				} else if (results[k] == Statement.EXECUTE_FAILED) {
					System.out.println("语句: " + sqls[k] + " 执行失败");
				}
			}//end for 2
		}//end if 2
		
		
		
		System.out.println("连接MySQL数据库成功！");
		con.close();
		System.out.println("成功关闭与MySQL数据库的连接！");
	}
	
	/**
	 * 判断数据库是否支持批处理
	 * @param con
	 * @return
	 */
	public static boolean supportBatch(Connection con) {
		try {
			// 得到数据库的元数据
			DatabaseMetaData md = con.getMetaData();
			return md.supportsBatchUpdates();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 执行一批SQL语句
	 * @param con	数据库的连接
	 * @param sqls	待执行的SQL数组
	 * @return
	 */
	public static int[] goBatch(Connection con, String[] sqls) {
		if (sqls == null) {
			return null;
		}
		Statement sm = null;
		try {
			sm = con.createStatement();
			// 将所有的SQL语句添加到Statement中
			for (int i = 0; i < sqls.length; i++) {
				sm.addBatch(sqls[i]);
			}
			// 一次执行多条SQL语句
			return sm.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(sm!=null){
				try {
					sm.clearBatch();
					sm.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
}
